package cn.jhc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.sun.org.apache.regexp.internal.recompile;

import cn.jhc.bean.NewsBean;

public class AnnouncementDAO {
	
	private Connection con = null;
	int PageCount; 
	
	public void addMent(int userid,String content) {
		String sql = "insert into announcement(user_id,context,date) "
				+ "values(?,?,now())";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, userid);
			st.setString(2, content);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public ArrayList<NewsBean> getAllAnn(int page,int newsCount){
		ArrayList<NewsBean> list = new ArrayList<NewsBean>();
		String sql = "select id,context,date from announcement order by id desc limit "
				+ (page-1) * (newsCount) + "," + newsCount + "";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);
			while(result.next()) {
				NewsBean s = new NewsBean();
				s.setId(result.getInt(1));
				s.setContext(result.getString(2));
				s.setDate(result.getDate(3));
				list.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public int getCount(int annCount) {
		String sql = "select count(*) from announcement";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				if(result.getInt(1)%annCount==0){
					this.PageCount=(result.getInt(1)/annCount);	
				}else{
					this.PageCount=(result.getInt(1)/annCount+1);	
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return PageCount ;
	}	
	public void deleteAnn(int id) {
		String sql = "delete from announcement where id = ?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public NewsBean getOneAnn(){
		NewsBean n = new NewsBean();
		String sql = "select context,date from announcement order by id desc limit 1";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);
			if (!result.next())
				n = null;
			else {
				n.setContext(result.getString(1));
				n.setDate(result.getDate(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return n;
	}
	public void setCon(Connection con) {
		this.con = con;
	}
}
